<?php
/**
 * PHP version 5
 *
 * This program is free software: you can redistribute it and/or modify
 * it under the terms of the GNU General Public License as published by
 * the Free Software Foundation, either version 3 of the License, or
 * (at your option) any later version.
 *
 * This program is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 * GNU General Public License for more details.
 *
 * You should have received a copy of the GNU General Public License
 * along with this program.  If not, see <http://www.gnu.org/licenses/>.
 *
 * @package    Textbook
 * @subpackage Textbook
 * @author     Aljaz Srebrnic <a2piratesoft@gmail.com>
 * @author     E. Pavletič
 * @copyright  2008 a2PirateSoftware
 * @license    GPL License v3 <http://www.gnu.org/copyleft/gpl.html>
 */

require_once('ZNF/Business/MDB2.php');
require_once('ZNF/Util/Utility.php');

class TextbookDAO {

  private $_db = null;
  private $_tablePrefix = null;

  /**
   * Constructor dobi povezavo z bazo in nastavi charset kot utf-8, da ne bo prislo do problemov s stresicami.
   *
   * @access public
   */
  public function __construct() {
    $this->_db = ZNF_Business_MDB2::getInstance();
    $this->_tablePrefix = ZNF_Business_MDB2::getTablePrefix();
    $this->_db->setCharset('utf8');
  }


  /**
   * Metoda countTextbooks
   *
   * Metoda vrne število uporabnikovih učbenikov. V primeru, da je uporabnik skrbnik vrne število učbenike vseh uporabnikov
   *
   * @param integer $userId Id uporabnika
   * @return integer Število učbenikov.
   * @access public
   */
  public function countTextbooks($userId = null) {
    $sql = 'SELECT COUNT(*) AS count FROM ' . $this->_tablePrefix . 'Textbook';
    if ($userId !== null)
      $sql .= " WHERE userId='".$userId."'";
    
    $row = $this->_db->queryRow($sql);
    if (PEAR::isError($row)) {
      throw new TextbookDAOException('errorTextbookCount - ['.__CLASS__.':'.__METHOD__.':'.__LINE__.'] '. $row->getMessage());
    }

    return $row['count'];
  }


  /**
   * Metoda getAll
   *
   * Metoda vrne seznam uporabnikovih učbenikov. V primeru, da je uporabnik skrbnik vrne seznam učbenikov vseh uporabnikov
   *
   * @param integer $userId Id uporabnika
   * @param integer $offset Zamik v tabeli
   * @param integer $limit Število naloženih učbenikov
   * @return integer Število učbenikov.
   * @access public
   */
  public function getAll($userId = null, $offset, $limit) {
    $sql = 'SELECT * FROM ' . $this->_tablePrefix . 'Textbook';
    if ($userId !== null)
      $sql .= " WHERE userId='".$userId."'";
    $sql .= ' LIMIT '.$limit.' OFFSET '.$offset;
    
    $rows = $this->_db->queryAll($sql);
    if (PEAR::isError($rows)) {
      throw new TextbookDAOException('errorTextbooksLoad - ['.__CLASS__.':'.__METHOD__.':'.__LINE__.'] '. $row->getMessage());
    }
    $rows = ZNF_Util_Utility::stripAllSlashes($rows);

    return $rows;
  }


  /**
   * Metoda getById
   *
   * Metoda vrne vse podatke o učbeniku, identificiranem z ID-jem.
   *
   * @param integer $id Id učbenika, o katerem želimo prejeti vse podatke.
   * @return array Tabela podatkov o uporabniku.
   * @access public
   */
  public function getById($id, $owner = false) {
    $sql = 'SELECT * FROM ' . $this->_tablePrefix . "Textbook WHERE Textbook.id='".$id."'";

    $row = $this->_db->queryRow($sql);
    if (PEAR::isError($row)) {
      throw new TextbookDAOException('errorTextbookLoad - ['.__CLASS__.':'.__METHOD__.':'.__LINE__.'] '. $row->getMessage());
    }
    if(count($row) > 0)
      $row = ZNF_Util_Utility::stripAllSlashes($row);

    return $row;
  }


  /**
   * Metoda insertTextbook
   *
   * Metoda vnese podatke o učbeniku v pod. bazo
   *
   * @param object $form Objekt, ki vsebuje podatke o učbeniku
   * @access public
   */
  public function insertTextbook($form) {
    $fields = 'userId, title, authors, publisher, published';
    $values = "'".$_SESSION['id']."', '".$form->getTitle()."', '".$form->getAuthors()."', '".$form->getPublisher()."', '".$form->getPublished()."'";
    if ($form->getIsbn() !== null) {
      $fields .= ', isbn';
      $values .= ", '". $form->getIsbn()."'";
    }
    if ($form->getPrice() !== null) {
      $fields .= ', price';
      $values .= ", '". $form->getPrice()."'";
    }
    if ($form->getForfree()) {
      $fields .= ', forfree';
      $values .= ", '1'";
    }
    if ($form->getForsale()) {
      $fields .= ', forsale';
      $values .= ", '1'";
    }
    if ($form->getForchange()) {
      $fields .= ', forchange';
      $values .= ", '1'";
    }
    if ($form->getComment() !== null) {
      $fields .= ', comment';
      $values .= ", '". $form->getComment()."'";
    }

    $sql = 'INSERT INTO '.$this->_tablePrefix.'Textbook ('.$fields.') VALUES ('.$values.')';

    $res = $this->_db->exec($sql);
    if (PEAR::isError($res)) {
      throw new TextbookDAOException('errorInsertTextbook - ['.__CLASS__.':'.__METHOD__.':'.__LINE__.'] '. $res->getMessage());
    }
  }


  /**
   * Metoda updateTextbook
   *
   * Metoda spremeni učbenikove podatke v pod. bazi
   *
   * @param object $form Objekt, ki vsebuje podatke o učbeniku
   * @access public
   */
  public function updateTextbook($form) {
    $values = "title = '".$form->getTitle()."', authors = '".$form->getAuthors()."', publisher = '".$form->getPublisher()."', published = '".$form->getPublished()."'";
    if ($form->getIsbn() !== null) {
      $values .= ",isbn  = '". $form->getIsbn()."'";
    }
    if ($form->getPrice() !== null) {
      $values .= ", price = '". $form->getPrice()."'";
    }
    if ($form->getForfree())
      $values .= ", forfree = '1'";
    else
      $values .= ", forfree = '0'";
    if ($form->getForsale())
      $values .= ", forsale = '1'";
    else
       $values .= ", forsale = '0'";
    if ($form->getForchange())
      $values .= ", forchange = '1'";
    else
      $values .= ", forchange = '0'";
    if ($form->getComment() !== null) {
      $values .= ", comment = '". $form->getComment()."'";
    }

    $sql = 'UPDATE '.$this->_tablePrefix.'Textbook SET '.$values." WHERE id = '".$form->getId()."'";

    $res = $this->_db->exec($sql);
    if (PEAR::isError($res)) {
      throw new TextbookDAOException('errorUpdateTextbook - ['.__CLASS__.':'.__METHOD__.':'.__LINE__.'] '. $res->getMessage());
    }
  }


  /**
   * Metoda deleteTextbooks
   *
   * Metoda izbrièe učbenike v pod. bazi
   *
   * @param String $ids Niz identifikatorjev iòbenikov, ki jih mora izbrisati iz baze, v obliki 'id', 'id', ... , 'id'
   * @access public
   */
  public function deleteTextbooks($ids) {
    $sql = 'DELETE FROM '.$this->_tablePrefix.'Textbook WHERE id IN ('.$ids.')';

    $res = $this->_db->exec($sql);
    if (PEAR::isError($res)) {
      throw new TextbookDAOException('errorDeleteTextbook - ['.__CLASS__.':'.__METHOD__.':'.__LINE__.'] '. $res->getMessage());
    }
  }


  /**
   * Metoda searchTextbooks
   *
   * Metoda poišče učbenike v pod. bazi
   *
   * @param int $userId Identifikator uporabnika
   * @param String $isbn ISBN učbenika
   * @param array $titleTerms Iskalni izrazi v naslovu
   * @param array $authors Tabela avtorjev
   * @param String $publisher Založnik
   * @param int $limit Max število zapisov, ki jih mora vrniti
   * @param int $offset Zamik v seznamu zapisov
   *
   * @return NULL v primeru, da učbenika ne najde, seznam učbenikov sicer
   * @access public
   */
  public function searchTextbooks($userId, $isbn, $titleTerms, $authors, $publisher, $offset, $limit) {
     $cond = $this->evalCond($isbn, $titleTerms, $authors, $publisher);
     if (is_null($cond))
       return null;

     if (!is_null($userId))
       $sql = "SELECT * FROM ".$this->_tablePrefix."Textbook T LEFT OUTER JOIN ".$this->_tablePrefix."Booking B ON (T.id = B.textbookId) WHERE ((B.userId IS NULL) OR (B.userId = '".$userId."')) AND (".$cond.') LIMIT '.$limit.' OFFSET '.$offset;
     else
        $sql = "SELECT * FROM ".$this->_tablePrefix."Textbook WHERE ".$cond.' LIMIT '.$limit.' OFFSET '.$offset;
 
     $rows = $this->_db->queryAll($sql);
     if (PEAR::isError($rows)) {
       throw new TextbookDAOException('errorTextbooksLoad - ['.__CLASS__.':'.__METHOD__.':'.__LINE__.'] '. $row->getMessage());
     }
     if (count($rows) == 0)
       return null;

     $rows = ZNF_Util_Utility::stripAllSlashes($rows);
     return $rows;
  }


  /**
   * Metoda countSearchedTextbooks
   *
   * Metoda preštejee učbenike v pod. bazi, ki zadoščajo pogojem
   *
   * @param String $isbn ISBN učbenika
   * @param array $titleTerms Iskalni izrazi v naslovu
   * @param array $authors Tabela avtorjev
   * @param String $publisher Založnik
   *
   * @access public
   */
   public function countSearchedTextbooks($isbn, $titleTerms, $authors, $publisher) {
     $cond = $this->evalCond($isbn, $titleTerms, $authors, $publisher);
     if (is_null($cond))
       return null;

     $sql = 'SELECT COUNT(*) AS count FROM '.$this->_tablePrefix.'Textbook WHERE '.$cond;
     
     $row = $this->_db->queryRow($sql);
     if (PEAR::isError($row)) {
       throw new TextbookDAOException('errorTextbookCount - ['.__CLASS__.':'.__METHOD__.':'.__LINE__.'] '. $row->getMessage());
     }
     
     return $row['count'];
  }


  /**
   * Metoda rememberRequest
   *
   * Metoda shrani podatke o iskani knjigi in uporabniku, ki je iskanje izvedel. Na tak način,
   * v primeru da nekdo vnese kasneje zahtevani učbenik, lasko sistem avtomatsko obvesti iakalca o novem vnosu.
   *
   * @param int $userId Identifikator uporabnika (iskalca)
   * @param String $isbn ISBN učbenika
   *
   * @access public
   */
   public function rememberRequest($userId, $isbn) {
     $sql = "INSERT INTO ".$this->_tablePrefix."SearchedTextbooks (userId, isbn) VALUES ('".$userId."', '".$isbn."')";
     
     $res = $this->_db->exec($sql);
     if (PEAR::isError($res)) {
       throw new TextbookDAOException('errorRememberSearchedTexbooks - ['.__CLASS__.':'.__METHOD__.':'.__LINE__.'] '. $res->getMessage());
     }
  }


 /**
   * Metoda getTextbookSeekers
   *
   * Metoda poišče vse uporabnike, ki so v preteklem obdobju (Constants::$searchedTextbooksRetainPeriod) iskali učbenik
   *
   * @param String $isbn ISBN učbenika
   *
   * @access public
   */
   public function getTextbookSeekers($isbn) {
     $sql = "SELECT * FROM ".$this->_tablePrefix."SearchedTextbooks WHERE isbn = '".$isbn."'";
     
     $rows = $this->_db->queryAll($sql);
     if (PEAR::isError($rows)) {
       throw new TextbookDAOException('errorSearchingTexbooks - ['.__CLASS__.':'.__METHOD__.':'.__LINE__.'] '. $res->getMessage());
     }
     $rows = ZNF_Util_Utility::stripAllSlashes($rows);

     return $rows;
  }



  /**
   * Metoda deleteOldSearchedTextbooks
   *
   * Metoda izbriše podatke o iskanih knjigah, ki so starejše od "Constants::$searchedTextbooksRetainPeriod" dni
   *
   * @param int $period Obdobje (v dnevih) po katerem so zahteve izbrisane iz baze
   *
   * @access public
   */
   public function deleteOldSearchedTextbooks($period) {
     $sql = 'DELETE FROM '.$this->_tablePrefix.'SearchedTextbooks WHERE DATEDIFF(NOW(), SearchedTextbooks.insertionDate) > '.$period;
     
     $res = $this->_db->exec($sql);
     if (PEAR::isError($res)) {
       throw new TextbookDAOException('errorDeleteSearchedTexbooks - ['.__CLASS__.':'.__METHOD__.':'.__LINE__.'] '. $res->getMessage());
     }
  }



  /**
   * Metoda deleteOldTextbooks
   *
   * Metoda izbriše podatke o knjigah, ki so starejše od "Constants::$textbooksRetainPeriod" dni
   *
   * @param int $period Obdobje (v dnevih) po katerem so ušbeniki izbrisani iz baze
   *
   * @access public
   */
   public function deleteOldTextbooks($period) {
     $sql = 'DELETE FROM '.$this->_tablePrefix.'Textbook WHERE DATEDIFF(NOW(), Textbook.offerdate) > '.$period;
     
     $res = $this->_db->exec($sql);
     if (PEAR::isError($res)) {
       throw new TextbookDAOException('errorDeleteOldTexbooks - ['.__CLASS__.':'.__METHOD__.':'.__LINE__.'] '. $res->getMessage());
     }
  }


  /**
   * Metoda evalCond
   *
   * Metoda sestavi niz iskalnih pogojev na osnovi danih podatkov. Služi pri iskanju učbenikov s strani uporabnikov.
   *
   * @param String $isbn ISBN učbenika
   * @param array $titleTerms Iskalni izrazi v naslovu
   * @param array $authors Tabela avtorjev
   * @param String $publisher Založnik
   *
   * @access private
   */
  private function evalCond($isbn, $titleTerms, $authors, $publisher) {
    $cond = '';
    if ($isbn !== null)
      $cond .= "isbn = '".$isbn."' ";
    if (count($titleTerms) > 0) {
      if (strlen($cond) > 0)
	$cond .= 'AND ';
      $cond .= '(';
      foreach ($titleTerms as $term)
	$cond .= "title LIKE '%".$term."%' OR ";
      $cond = substr($cond, 0, strlen($cond)-4);
      $cond .= ') ';
    }
    if (count($authors) > 0) {
      if (strlen($cond) > 0)
	$cond .= 'AND ';
      $cond .= '(';
      foreach ($authors as $author)
	$cond .= "authors LIKE '%".$author."%' OR ";
      $cond = substr($cond, 0, strlen($cond)-4);
      $cond .= ') ';
    }
    if ($publisher !== null) {
      if (strlen($cond) > 0)
	$cond .= 'AND ';
      $cond .= "publisher = '".$publisher."'";
    }

    if (strlen($cond) == 0)
      return null;

    return $cond;
  }
}


class TextbookDAOException extends Exception {}
?>